using System;
using System.Collections.Generic;
using System.Data;
//using System.Data.SqlServerCe;
//using System.Data.Odbc;

/*
	Core classe
*/
namespace SqlCeDriver
{
	/// <summary>
	/// Summary description for Helper.
	/// </summary>
	public class Helper
	{
		public Helper(SqlCEDataAccessor accessor)
		{
			this.dataAccessor = accessor;
		}

		public static String MakeSearchClause(String seek)
		{
			String clause = String.Format("like \'%{0}%\'", seek.Trim());
			if (seek.Length == 0)
				return clause;
			// get beginning and end chars
			char first = seek[0];
			char last = seek[seek.Length - 1];
			// use regex 
			if (first == '^' && last == '$')
				return String.Format("= '{0}'", seek.Substring(1, seek.Length - 2));
			// assume it will be "like '%xxx%'
			if (first == '^')
				return String.Format("like \'{0}%\'", seek.Substring(1, seek.Length - 1).Trim());
			else
				// assume it will be "like '%xxx%'
				if (last == '$')
					return String.Format("like \'%{0}\'", seek.Substring(0, seek.Length - 1));
			return clause;
		}

		public static String SafeTableName(String tableName)
		{
			// empty string: reflect it back
			if (tableName.Length == 0) return tableName;
			// already escaped apparently: nothing to do
			if (tableName[0] == '[' && tableName[tableName.Length - 1] == ']')
				return tableName;
			// 
			// Experimental: if a sub query then skip
			//
			if (tableName.Replace(" ","").ToUpper().IndexOf("(SELECT") != -1) return tableName;
			// no schema: just [ tablename ]
			if (tableName.IndexOf('.') == -1)
				return "[" + tableName + "]";
			// Schema.tablename -> [Schema].[tablename]
			return "[" + tableName.Replace(".", "].[") + "]";
		}

		/*
			Port from SqlServr. Not sure its necessary, but keeps code common
			Breaks a table name into schema table
			but reverses the order so that the table name is always 1st.
		*/
		public static String[] ParseTableName(String tableName)
		{
			String[] result = tableName.Split('.');
			if (result.GetLength(0) > 1)
			{
				String tmp = result[1];
				result[1] = result[0];
				result[0] = tmp;
			}
			return result;
		}

		public static String SafeSqlField(String field)
		{
			if (field.Length == 0)
				return field;
			if (field[0] == '[' && field[field.Length - 1] == ']')
				return field;
			// hacked fix for aliased fields.
			if (field.IndexOf('.') > 0)
				return field;
			return "[" + field.Trim() + "]"; // does not allow for fields with leading & trailing spaces
		}

		public DataTable GenericQuery(String strConnectionString, String sql, String tableName)
		{
			// queries with spaces in are split up
			String[] commandList = MakeCommandList(sql);
			return this.dataAccessor.Query(strConnectionString, commandList, tableName);
		}

		// Compatibility
		public String GenericExecute(String strConnectionString, String sql, out bool success)
		{
			String[] commandList = MakeCommandList(sql);
			try
			{
				DataTable dt = this.dataAccessor.Query(strConnectionString, commandList, "result");
				success = true;
			}
			catch (Exception sqlExc)
			{
				success = false;
				return sqlExc.Message;
			}
			return ""; 
		}

		// New tactic
		// split on \n replace \r with ""
		// right trim each line
		// 
		private static string[] MakeCommandList(string sql)
		{
			String[] a = sql.Replace("\r", "").Split('\n');
			List<String> commands = new List<string>();
			String newCommand = "";
			foreach (String command in a)
			{
				if (newCommand.Length > 0) newCommand += "\r\n";
				newCommand += command;
				String line = command.TrimEnd();
				if (line.Length > 0)
					if (line.LastIndexOf(';') == line.Length - 1)
					{
						commands.Add(newCommand);
						newCommand = "";
					}
			}
			if (newCommand.Length > 0) commands.Add(newCommand);
			return commands.ToArray();
		}

		public bool TestConnection(String strConnectionString, out String message)
		{
			message = ""; // OK 
			String testSql = "select getdate() as TimeNow";
			try
			{
				DataTable dt = this.GenericQuery(strConnectionString, testSql, "TestConnection");
				message = "Connected OK at:" + dt.Rows[0][0].ToString();
			}
			catch (Exception e)
			{
				message = String.Format("{0}", e.Message);
				// may not work if connection fails
				return false;
			}
			return true;
		}
	
		public static SqlTableSchema MakeSchemaFor(String tableName, SqlCEDataAccessor accessor, String connStr)
		{
		    // 3 is a magic number for all SQL CE until it is refactored.
			String commandTemplate = SqlCeTemplates.Templates.FetchTemplate(3, DbView.Commands.Cmnd.Fields);
		    String commandString = String.Format(commandTemplate, tableName, "");
		    DataSet dset = new DataSet();
		    DataTable schemaTable = new Helper(accessor).GenericQuery(connStr, commandString, tableName);
		    schemaTable.TableName = tableName;
		    SqlTableSchema schema = new SqlTableSchema();
		    schema.Populate(schemaTable, "ColumnName", "Type", "Length", "Dp", "PKey?", "Auto?",
		                                 "FKey?", "Index?", "Calc?", "Def?", "Default", "Nulls?");

		    return schema;
		}

		public bool ResetAutoIdentity(String strConnectionString, String tableName)
		{
			// has it got an auto-identity key - no then no action
			String sql = String.Format("select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '{0}' and  AUTOINC_NEXT IS NOT NULL", tableName);
			DataTable res = this.GenericQuery(strConnectionString, sql, "Result");
			if (res.Rows.Count == 0) return true; // true = OK
			String autoField = res.Rows[0][0].ToString();
			// find highest value in the table
			sql = String.Format("select max({1}), count({1}) from {0}", tableName, autoField);
			res = this.GenericQuery(strConnectionString, sql, "Result");
			int max = (int)res.Rows[0][1]; // obtain count
			if (max == 0)					// no rows
				max = 0;
			else
				max = (int)res.Rows[0][0];
			// set to that + 1
			sql = String.Format("alter table [{0}] ALTER COLUMN {1} IDENTITY ({2},1); ",tableName, autoField, max + 1);
			bool success;
			this.GenericExecute(strConnectionString, sql, out success);
			return success;
		}
		SqlCEDataAccessor dataAccessor;
	}

	class DataTableHelpers
	{
		/* as you always forget
			dt.Rows.Add(new object[] { 1, "<clear filter>", "" });
		*/
		public static DataTable MakeDataTable(String name, String columnNames, String columnTypes)
		{
			String[] colNames = columnNames.Split('|');
			if (colNames.GetLength(0) != columnTypes.Length)
			{
				int n1 = colNames.GetLength(0);
				int n2 = columnTypes.Length;
				String msg = String.Format("Invalid table specification for MakeDataTable\n" +
										   "The number of columns must equal the number of column type characters.\n" +
										   "There are {0} names in {1}\n" +
										   "There are {2} chars in {3}", n1, columnNames, n2, columnTypes);
				throw new Exception(msg);
			}
			DataTable dt = new DataTable(name);
			for (int idx = 0; idx < colNames.GetLength(0); ++idx)
			{
				String colName = colNames[idx];
				String typeName = DataTableHelpers.ResolveType(columnTypes[idx]);

				DataColumn col = new DataColumn();
				col.ColumnName = colName;
				col.DataType = System.Type.GetType(typeName);
				dt.Columns.Add(col);
			}
			return dt;
		}

		public static DataTable MakeSortedTable(DataTable source, String field, bool asc)
		{
			DataTable target = source.Clone(); // copy structure so rows can be received
			String sortClause = String.Format("{0} {1}", field, (!asc) ? "DESC" : "");
			DataRow[] sorted = source.Select("", sortClause);
			foreach (DataRow sortedRow in sorted)
				target.ImportRow(sortedRow);
			return target;
		}

		private static String ResolveType(char c)
		{
			switch (c)
			{
				case 'S': return "System.String";
				case 'I': return "System.Int32";
				case 'N': return "System.Double";
				case 'D': return "System.DateTime";
				default:
					throw new Exception(String.Format("Invalid Type code {0} - expecting S,I,N,D", c));
			}
		}
	}
}